﻿using NHibernate;
using NHibernate.Expression;
using NHModel.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace NHDemo
{
    public class UserInfoBll
    {
        protected ISession session = NHibernateHelper.NHibernateHelper.GetCurrentSession();

        #region 基本操作
        /// <summary>
        /// 通过Id来获取
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public UserInfo Get(int Id)
        {
            return (UserInfo)session.Get(typeof(UserInfo), Id);
        }

        /// <summary>
        /// 添加对象
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int Add(UserInfo user)
        {
            object t = session.Save(user);//返回的是主键
            return 1;
        }

        /// <summary>
        /// 更新对象
        /// </summary>
        /// <param name="user"></param>
        public void Update(UserInfo user)
        {
            session.Update(user);
        }

        /// <summary>
        /// 删除对象
        /// </summary>
        /// <param name="Id"></param>
        public void Delete(int Id)
        {
            UserInfo info = new UserInfo();
            info.Id = Id;
            //其他属性不起作用
            session.Delete(info);
        }
        #endregion

        #region HQL
        /*
         *1.区分大小写 所有的操作都是针对实体的
         *2.必须以select或者from开头
         */

        /// <summary>
        /// 查找所有
        /// </summary>
        /// <returns></returns>
        public List<UserInfo> SearchAll_HQL()
        {
            IQuery query = session.CreateQuery("from UserInfo");
            return query.List<UserInfo>().ToList();
        }

        /// <summary>
        /// Get
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public UserInfo Get_HQL(int Id)
        {
            //2种写法
            //select u from UserInfo u where u.Id=:Id
            //from UserInfo  where Id=:Id
            IQuery query = session.CreateQuery("from UserInfo where Id=:Id");
            query.SetInt32("Id", Id);
            return query.List<UserInfo>()[0];
        }

       
        /// <summary>
        /// 查找姓名
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public string GetName_HQL(int Id)
        {
            //只能使用别名的方法写
            IQuery query = session.CreateQuery("select u.Name from UserInfo u where u.Id=:Id");
            query.SetInt32("Id", Id);
            IList<string> list=query.List<string>();
            return list[0];
        }
        #endregion

        #region SQL
        /*
         *原生SQL写法
         *不区分大小写
         */
        /// <summary>
        /// Get
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public UserInfo Get_SQL(int Id)
        {
            //addEntity的这个实体必须是 hibernate映射的
            ISQLQuery query = session.CreateSQLQuery("select * from userinfo where id=:Id").AddEntity(typeof(UserInfo));
            query.SetInt32("Id", Id);
            return query.List<UserInfo>()[0];
        }
        /// <summary>
        /// GetName
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public string GetName_SQL(int Id)
        {
            //addEntity的这个实体必须是 hibernate映射的
            ISQLQuery query = session.CreateSQLQuery("select name from userinfo where id=:Id");
            query.SetInt32("Id", Id);
            //设置返回值类型
            query.AddScalar("name", NHibernateUtil.String);
            //将返回列映射到HashTable
            //或者也可以映射到一个实体 Transformers.AliasToBean 注意大小写
            query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToEntityMap);
            IList<Hashtable> lstTable = query.List < Hashtable>();
            return lstTable[0]["name"].ToString();
        }


        #endregion




    }
}